# Report-ServicePrincipalConnections.PS1
# An example of reading the Entra ID Sign in log to report on service principal sign-ins 

# V1.0  1-Feb-2025
#
# GitHub link: https://github.com/12Knocksinna/Office365itpros/blob/master/Report-ServicePrincipalConnections.PS1

Connect-MgGraph -Scopes AuditLog.Read.All, Directory.Read.All 

# Find what our tenant is
$Tenant = Get-MgOrganization

# Get the last 5000 sign-ins for service principals (going back a maximum of 30 days)
Write-Host "Finding service principal sign-ins..."
[array]$AuditRecords = Get-MgBetaAuditLogSignIn -Filter "(signInEventTypes/any(t:t eq 'servicePrincipal'))" -Top 5000 -Sort "createdDateTime DESC"
If (!$AuditRecords) { 
    Write-Host "Unable to find audit records - exiting" ; break 
}   Else {
    Write-Host "Found $($AuditRecords.Count) audit records"
}

Write-Host "Processing audit records..."
$Report = [System.Collections.Generic.List[Object]]::new()
ForEach ($Record in $AuditRecords) {
    # If the record is not generated by a connection from our tenant, find out the tenant name
    If ($Record.additionalProperties.appOwnerTenantId -eq $Tenant.Id) {
        $TenantName = $Tenant.DisplayName
        $TenantId = $Tenant.Id
    } Else {
        $Uri = ("https://graph.microsoft.com/V1.0/tenantRelationships/findTenantInformationByTenantId(tenantId='{0}')" -f $Record.additionalProperties.appOwnerTenantId.ToString())
        $ExternalTenantData = Invoke-MgGraphRequest -Uri $Uri -Method Get
        $TenantName = $ExternalTenantData.DisplayName
    }
    # If the sign-in attempt was unsucccessful, report the error
    If ($Record.Status.errorCode -eq 0) {
        $SignInStatus = "Success"
    } Else {
        Switch ($Record.Status.errorCode) {  # See https://learn.microsoft.com/en-us/entra/identity-platform/reference-error-codes
            "70001" {
                $SignInStatus = "Failure (app is disabled)" }
            "700030" { 
                $SignInStatus = "Failure (Invalid certificate)" }
            "7000222" { 
                $SignInStatus = "Failure (Expired client secret)" }
            "7000215" {
                $SignInStatus = "Failure (Invalid client secret)" } 
            "Default" {
                $SignInStatus = ("Failure (Error Code: {0})" -f $Record.Status.errorCode)
            }
        }
    }

    # Generate the report line
    $ReportLine = [PSCustomObject][Ordered]@{ 
        Timestamp           = $Record.CreatedDateTime
        Application         = $Record.AppDisplayName
        Credential          = $Record.ClientCredentialType
        Status              = $SignInStatus
        IPAddress           = $Record.IPAddress
        FromLocation        = $Record.Location.City
        Resource            = $Record.ResourceDisplayName
        AppId               = $Record.AppId
        ServicePrincipalId  = $Record.ServicePrincipalId
        OwningTenantId      = $TenantId
        OwningTenant        = $TenantName
    }
    $Report.Add($ReportLine)
}

$ToDate = (Get-Date $AuditRecords[0].CreatedDateTime -format 'dd-MMM-yyyy')
$FromDate = (Get-Date $AuditRecords[-1].CreatedDateTime -format 'dd-MMM-yyyy')
$Title = ("Service Principal Sign-ins from {0} to {1}" -f $FromDate, $ToDate)

Write-Host "Generating report..."
If (Get-Module ImportExcel -ListAvailable) {
    $ExcelGenerated = $True
    Import-Module ImportExcel -ErrorAction SilentlyContinue
    $ExcelOutputFile = ((New-Object -ComObject Shell.Application).Namespace('shell:Downloads').Self.Path) + "\Service Principal SignIns.xlsx"
    If (Test-Path $ExcelOutputFile) {
        Remove-Item $ExcelOutputFile -ErrorAction SilentlyContinue
    }
    $Report | Export-Excel -Path $ExcelOutputFile -WorksheetName "Service Principal Sign-in Logs" -Title $Title -TitleBold -TableName "ServicePrincipalSignIns" 
} Else {
    $CSVOutputFile = ((New-Object -ComObject Shell.Application).Namespace('shell:Downloads').Self.Path) + "\Service Principal SignIns.CSV"
    $Report | Export-Csv -Path $CSVOutputFile -NoTypeInformation -Encoding Utf8
}
 
If ($ExcelGenerated) {
    Write-Host ("An Excel report detailing Service Principal audit log sign-in records is available in {0}" -f $ExcelOutputFile)
} Else {    
    Write-Host ("A CSV report of underused Microsoft 365 Copilot licenses is available in {0}" -f $CSVOutputFile)
}  


# An example script used to illustrate a concept. More information about the topic can be found in the Office 365 for IT Pros eBook https://gum.co/O365IT/
# and/or a relevant article on https://office365itpros.com or https://www.practical365.com. See our post about the Office 365 for IT Pros repository # https://office365itpros.com/office-365-github-repository/ for information about the scripts we write.

# Do not use our scripts in production until you are satisfied that the code meets the need of your organization. Never run any code downloaded from the Internet without
# first validating the code in a non-production environment.
